home *** CD-ROM | disk | FTP | other *** search
/ Winzipper / Winzipper_ISO.iso / programming / oracle7 7.2 / DB / UTIL72 / CATLDR.SQL < prev    next >
Encoding:
Text File  |  1995-05-09  |  10.1 KB  |  286 lines
  1. rem 
  2. rem $Header: catldr.sql 7020100.1 94/09/23 22:14:22 cli Generic<base> $ ulview.sql 
  3. rem
  4. Rem Copyright (c) 1990 by Oracle Corporation
  5. Rem NAME
  6. Rem    catldr.sql
  7. Rem  FUNCTION
  8. Rem    Views for the direct path of the loader
  9. Rem  NOTES
  10. Rem    This script must be run while connected as SYS or INTERNAL.
  11. Rem  MODIFIED
  12. Rem     wmaimone   05/06/94 -  #184921 run as sys/internal
  13. Rem     ksudarsh   04/07/94 -  update loader_constraints_info
  14. Rem     ksudarsh   02/06/94 -  merge changes from branch 1.3.710.2
  15. Rem     ksudarsh   02/04/94 -  fix authorizations
  16. Rem     jbellemo   12/17/93 -  merge changes from branch 1.3.710.1
  17. Rem     jbellemo   11/29/93 -  #170173: change uid to userenv schemaid
  18. Rem     ksudarsh   11/02/92 -  pdl changes 
  19. Rem     tpystyne   11/22/92 -  use create or replace view 
  20. Rem     glumpkin   10/25/92 -  Renamed from ULVIEW.SQL 
  21. Rem     cheigham   04/28/92 -  users should see info only on tables on which th
  22. Rem     cheigham   10/26/91 -  Creation 
  23. Rem     cheigham   10/07/91 -  add lists, groups to tab,ind views
  24. Rem     cheigham   09/30/91 -  merge changes from branch 1.3.50.2 
  25. Rem     cheigham   09/23/91 -  fix cdef$ column reference 
  26. Rem     cheigham   08/27/91 -  add ts# to loader_tab_info: 
  27. Rem     cheigham   04/11/91 -         expand loader_constraint_info 
  28. Rem   Heigham    09/26/90 - fix v7 LOADER_TRIGGER_INFO def
  29. Rem   Heigham    07/16/90 - remove duplicate grant
  30. Rem   Heigham    06/28/90 - add v$parameters grant
  31. Rem   Heigham    01/22/90 - Creation
  32. Rem
  33. rem 
  34.  
  35. create or replace view LOADER_COL_INFO
  36. (TABNAME, OWNER, COLNAME, SEGCOL, TYPE, LENGTH, PRECISION, SCALE, NONULL,
  37. OFFSET)
  38. as
  39. select o.name, u.name, c.name, c.segcol#, c.type#, c.length, c.precision,
  40. c.scale, c.null$, c.offset
  41. from sys.col$ c, sys.obj$ o, sys.user$ u
  42. where o.obj# = c.obj#
  43. and o.owner# = u.user#
  44.  and (o.owner# = userenv('schemaid')
  45.        or o.obj# in
  46.             (select oa.obj#
  47.              from sys.objauth$ oa
  48.              where grantee# in ( select kzsrorol
  49.                                  from x$kzsro
  50.                                )
  51.         )
  52.        or /* user has system privileges */
  53.           exists (select null from v$enabledprivs
  54.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  55.                                         -47 /* SELECT ANY TABLE */,
  56.                                         -48 /* INSERT ANY TABLE */,
  57.                                         -49 /* UPDATE ANY TABLE */,
  58.                                         -50 /* DELETE ANY TABLE */)
  59.                   )
  60.       )
  61. /
  62. drop public synonym LOADER_COL_INFO
  63. /
  64. create public synonym LOADER_COL_INFO for LOADER_COL_INFO
  65. /
  66. grant select on LOADER_COL_INFO to public
  67. /
  68. create or replace view LOADER_TAB_INFO
  69. (NAME, FILENO, BLOCKNO, NUMCOLS, OWNER, OBJECTNO, TABLESPACENO, LISTS, GROUPS)
  70. as
  71. select o.name, t.file#, t.block#, t.cols, u.name, t.obj#, t.ts#, s.lists,
  72. s.groups
  73. from sys.tab$ t, sys.obj$ o, sys.user$ u, sys.seg$ s
  74. where t.obj# = o.obj#
  75. and o.owner# = u.user#
  76. and t.file# = s.file#
  77. and t.block# = s.block#
  78.  and (o.owner# = userenv('schemaid')
  79.        or o.obj# in
  80.             (select oa.obj#
  81.              from sys.objauth$ oa
  82.              where grantee# in ( select kzsrorol
  83.                                  from x$kzsro
  84.                                )
  85.         )
  86.        or /* user has system privileges */
  87.           exists (select null from v$enabledprivs
  88.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  89.                                         -47 /* SELECT ANY TABLE */,
  90.                                         -48 /* INSERT ANY TABLE */,
  91.                                         -49 /* UPDATE ANY TABLE */,
  92.                                         -50 /* DELETE ANY TABLE */)
  93.                   )
  94.       )
  95. /
  96. drop public synonym LOADER_TAB_INFO
  97. /
  98. create public synonym LOADER_TAB_INFO for LOADER_TAB_INFO
  99. /
  100. grant select on LOADER_TAB_INFO to PUBLIC
  101. /
  102. create or replace view LOADER_IND_INFO
  103. (NAME, OWNER_NAME, TABLESPACENO, PCTFRE, FILENO, BLOCKNO, NUMCOLS, OWNERNO, 
  104. UNIQUENESS, OBJECTNO, LISTS, GROUPS)
  105. as 
  106. select o.name, u.name, i.ts#, i.pctfree$, i.file#, i.block#, i.cols, o.owner#,
  107. i.unique$, i.obj#, s.lists, s.groups
  108. from sys.ind$ i, sys.obj$ o, sys.user$ u, sys.seg$ s
  109. where i.obj# = o.obj#
  110. and o.owner# = u.user#
  111. and i.file# = s.file#
  112. and i.block# = s.block#
  113.  and (o.owner# = userenv('schemaid')
  114.        or i.bo# in
  115.             (select oa.obj#
  116.              from sys.objauth$ oa
  117.              where grantee# in ( select kzsrorol
  118.                                  from x$kzsro
  119.                                )
  120.         )
  121.        or /* user has system privileges */
  122.           exists (select null from v$enabledprivs
  123.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  124.                                         -47 /* SELECT ANY TABLE */,
  125.                                         -48 /* INSERT ANY TABLE */,
  126.                                         -49 /* UPDATE ANY TABLE */,
  127.                                         -50 /* DELETE ANY TABLE */)
  128.                   )
  129.       )
  130. /
  131. drop public synonym LOADER_IND_INFO
  132. /
  133. create public synonym LOADER_IND_INFO for LOADER_IND_INFO
  134. /
  135. grant select on LOADER_IND_INFO to PUBLIC
  136. /
  137. create or replace view LOADER_INDCOL_INFO
  138. (INDEX_NAME, INDEX_OWNER, POSITION, SEGCOL)
  139. as 
  140. select idx.name, io.name, ic.pos#, ic.segcol#
  141. from sys.user$ io, sys.obj$ idx, sys.icol$ ic
  142. where idx.obj# = ic.obj#
  143. and  idx.owner# = io.user# 
  144. and (idx.owner# = userenv('schemaid')
  145.        or ic.bo# in
  146.             (select oa.obj#
  147.              from sys.objauth$ oa
  148.              where grantee# in ( select kzsrorol
  149.                                  from x$kzsro
  150.                                )
  151.         )
  152.        or /* user has system privileges */
  153.           exists (select null from v$enabledprivs
  154.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  155.                                         -47 /* SELECT ANY TABLE */,
  156.                                         -48 /* INSERT ANY TABLE */,
  157.                                         -49 /* UPDATE ANY TABLE */,
  158.                                         -50 /* DELETE ANY TABLE */)
  159.                   )
  160.     )
  161. /
  162. drop public synonym LOADER_INDCOL_INFO
  163. /
  164. create public synonym LOADER_INDCOL_INFO for LOADER_INDCOL_INFO
  165. /
  166. grant select on LOADER_INDCOL_INFO to PUBLIC
  167. /
  168. create or replace view LOADER_PARAM_INFO
  169. (BLOCKSZ, SERIALIZABLE)
  170. as 
  171. select v1.value, v2.value from v$parameter v1, v$parameter v2
  172. where v1.name = 'db_block_size' and  v2.name = 'serializable'
  173. /
  174. drop public synonym LOADER_PARAM_INFO
  175. /
  176. create public synonym LOADER_PARAM_INFO for LOADER_PARAM_INFO
  177. /
  178. grant select on LOADER_PARAM_INFO to PUBLIC
  179. /
  180. remark
  181. remark VIEWS FOR FIXED TABLES OF STATISTICS
  182. remark
  183. remark CONTROL BLOCK STATS 
  184. remark
  185. create or replace view v_$loadcstat as select * from v$loadcstat;
  186. drop public synonym v$loadcstat;
  187. create public synonym v$loadcstat for v_$loadcstat;
  188. grant select on v_$loadcstat to public;
  189. remark
  190. remark TABLE STATS 
  191. remark
  192. create or replace view v_$loadtstat as select * from v$loadtstat;
  193. drop public synonym v$loadtstat;
  194. create public synonym v$loadtstat for v_$loadtstat;
  195. grant select on v_$loadtstat to public;
  196. remark
  197. remark VIEWS FOR V7
  198. create or replace view LOADER_CONSTRAINT_INFO
  199. (OWNER, CONSTRAINT_NAME, CONSTRAINT_NUMBER, TYPE, TABLE_NAME, ENABLED, 
  200.  NOTNULL, NUMCOLS)
  201. as
  202.    select u.name, con.name, cd.con#, cd.type,
  203.    o.name, cd.enabled, col.null$, cd.cols
  204.    from sys.con$ con, sys.user$ u, sys.cdef$ cd, sys.obj$ o,
  205.    sys.ccol$ cco, sys.col$ col
  206.    where con.owner# = u.user#
  207.    and con.con# = cd.con#
  208.    and cd.obj# = o.obj#
  209.    and cco.con# = con.con#
  210.    and col.obj# = cco.obj#
  211.    and col.col# = cco.col#
  212.    and (con.owner# = userenv('schemaid')
  213.        or o.obj# in
  214.             (select oa.obj#
  215.              from sys.objauth$ oa
  216.              where grantee# in ( select kzsrorol
  217.                                  from x$kzsro
  218.                                )
  219.         )
  220.        or /* user has system privileges */
  221.           exists (select null from v$enabledprivs
  222.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  223.                                         -47 /* SELECT ANY TABLE */,
  224.                                         -48 /* INSERT ANY TABLE */,
  225.                                         -49 /* UPDATE ANY TABLE */,
  226.                                         -50 /* DELETE ANY TABLE */)
  227.                   )
  228.       )
  229. /
  230. drop public synonym LOADER_CONSTRAINT_INFO
  231. /
  232. create public synonym LOADER_CONSTRAINT_INFO for LOADER_CONSTRAINT_INFO
  233. /
  234. grant select on LOADER_CONSTRAINT_INFO to PUBLIC
  235. /
  236. create or replace view LOADER_TRIGGER_INFO
  237. (OWNER, TRIGGER_NAME, TABLE_NAME, ENABLED)
  238. as
  239.    select u.name, o1.name, o.name, t.enabled
  240.    from sys.obj$ o, sys.obj$ o1, sys.user$ u, sys.trigger$ t
  241.    where t.baseobject = o.obj#
  242.    and o.owner# = u.user#
  243.    and t.obj# = o1.obj#
  244.  and (o.owner# = userenv('schemaid')
  245.        or o.obj# in
  246.             (select oa.obj#
  247.              from sys.objauth$ oa
  248.              where grantee# in ( select kzsrorol
  249.                                  from x$kzsro
  250.                                )
  251.         )
  252.        or /* user has system privileges */
  253.           exists (select null from v$enabledprivs
  254.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  255.                                         -47 /* SELECT ANY TABLE */,
  256.                                         -48 /* INSERT ANY TABLE */,
  257.                                         -49 /* UPDATE ANY TABLE */,
  258.                                         -50 /* DELETE ANY TABLE */)
  259.                   )
  260.      )
  261. /
  262. drop public synonym LOADER_TRIGGER_INFO
  263. /
  264. create public synonym LOADER_TRIGGER_INFO for LOADER_TRIGGER_INFO
  265. /
  266. grant select on LOADER_TRIGGER_INFO to PUBLIC
  267. /
  268. remark
  269. remark VIEWS for Parallel Data Loader
  270. remark
  271. drop view LOADER_FILE_TS
  272. /
  273. create view LOADER_FILE_TS
  274. (TABLESPACENO, FILENAME, FILENO)
  275. as
  276.    select file$.ts#, v$dbfile.name, file$.file# 
  277.    from file$, v$dbfile
  278.    where file$.file# = v$dbfile.file#
  279. /
  280. drop public synonym LOADER_FILE_TS
  281. /
  282. create public synonym LOADER_FILE_TS for LOADER_FILE_TS
  283. /
  284. grant select on LOADER_FILE_TS to public
  285. /
  286.